﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using ProjectCommonLibs.consts;
using ProjectCommonLibs.enums;
using ProjectCommonLibs.models;
using ProjectCommonLibs.utils;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ProjectCommonLibs.repository
{
    public class ExcelRepository
    {
        private readonly string fileName;
        private List<long> dataList;
        public ExcelRepository(string fileName)
        {
            this.fileName = fileName;
        }

        public List<double[]> readResourceExcel()
        {
            List<double[]> result = new List<double[]>();
            try
            {
                XSSFWorkbook workbook = new XSSFWorkbook(fileName);
                ISheet sheet = workbook.GetSheet("Sheet2");
                for (int i = 3; i < 154; i++)
                {
                    var row = sheet.GetRow(i);
                    double[] d = new double[26];
                    for (int j = 0; j < 26; j++)
                    {
                        d[j] = row.GetCell(j).NumericCellValue;
                    }
                    result.Add(d);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                Console.WriteLine(ex.StackTrace);
            }
            return result;
        }

        public List<SurfaceModel> GetSurfaceModels()
        {
            List<SurfaceModel> surfaceModels = new List<SurfaceModel>();
            try
            {
                XSSFWorkbook workBook = new XSSFWorkbook(fileName);
                var sheet = workBook.GetSheetAt(0);
                for (int i = 0; i < sheet.LastRowNum; i++)
                {
                    var row = sheet.GetRow(i);
                    surfaceModels.Add(new SurfaceModel()
                    {
                        temprature = (int)row.GetCell(0).NumericCellValue,
                        radiation = (int)row.GetCell(1).NumericCellValue,
                        v1 = (int)row.GetCell(2).NumericCellValue,
                        v2 = (int)row.GetCell(3).NumericCellValue
                    });
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                Console.WriteLine(e.StackTrace);
            }
            return surfaceModels;
        }

        public List<DongTaiDataModel> readDongTaiExcel()
        {
            List<DongTaiDataModel> models = new List<DongTaiDataModel>();
            try
            {
                XSSFWorkbook workBook = new XSSFWorkbook(fileName);
                var sheet = workBook.GetSheetAt(0);
                for (int i = 1; i < sheet.LastRowNum; i++)
                {
                    models.Add(deserializeExcelRow(sheet, i,Projects.DONGTAI) as DongTaiDataModel);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                Console.WriteLine(e.StackTrace);
            }
            return models;
        }

        public List<RoofPhotovoltaicModel> readRoofPhotovoltaicExcel()
        {
            List<RoofPhotovoltaicModel> models = new List<RoofPhotovoltaicModel>();
            try
            {
                XSSFWorkbook workBook = new XSSFWorkbook(fileName);
                var sheet = workBook.GetSheetAt(0);
                for (int i = 1; i < sheet.LastRowNum; i++)
                {
                    models.Add(deserializeExcelRow(sheet, i, Projects.ROOF_PHOTOVOLTAIC) as RoofPhotovoltaicModel);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                Console.WriteLine(e.StackTrace);
            }
            return models;
        }

        public List<RoofWeatherModel> readRoofWeatherExcel()
        {
            List<RoofWeatherModel> models = new List<RoofWeatherModel>();
            try
            {
                XSSFWorkbook workBook = new XSSFWorkbook(fileName);
                var sheet = workBook.GetSheetAt(0);
                for (int i = 1; i < sheet.LastRowNum; i++)
                {
                    models.Add(deserializeExcelRow(sheet, i, Projects.ROOF_WEATHER) as RoofWeatherModel);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                Console.WriteLine(e.StackTrace);
            }
            return models;
        }

        public List<SeasonalEfficiencyModel> readSeasonalEfficiencyDataHighVersion()
        {
            List<SeasonalEfficiencyModel> models = new List<SeasonalEfficiencyModel>();
            try
            {
                XSSFWorkbook workBook = new XSSFWorkbook(new FileStream(fileName, FileMode.Open, FileAccess.Read));
                var sheet = workBook.GetSheetAt(0);
                for (int i = 0; i < sheet.LastRowNum; i++)
                {
                    var row = sheet.GetRow(i);
                    models.Add(new SeasonalEfficiencyModel() { power = row.GetCell(0).NumericCellValue, efficiency = row.GetCell(1).NumericCellValue });
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                Console.WriteLine(e.StackTrace);
            }
            return models;
        }

        public List<SeasonalEfficiencyModel> readSonsonalEfficiencyExcel()
        {
            List<SeasonalEfficiencyModel> models = new List<SeasonalEfficiencyModel>();
            try
            {
                HSSFWorkbook workBook = new HSSFWorkbook(new FileStream(fileName,FileMode.Open,FileAccess.Read));
                var sheet = workBook.GetSheetAt(0);
                for (int i = 0; i < sheet.LastRowNum; i++)
                {
                    var row = sheet.GetRow(i);
                    models.Add(new SeasonalEfficiencyModel() {power = row.GetCell(0).NumericCellValue,efficiency = row.GetCell(1).NumericCellValue });
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                Console.WriteLine(e.StackTrace);
            }
            return models;
        }
        public List<DaFengDataModel> readDaFengDataExcel()
        {
            List<DaFengDataModel> models = new List<DaFengDataModel>();
            XSSFWorkbook workBook = new XSSFWorkbook(new FileStream(fileName, FileMode.Open, FileAccess.Read));
            var sheet = workBook.GetSheetAt(0);
            for (int i = 80; i < 104; i++)
            {
                var row = sheet.GetRow(i);
                models.Add(new DaFengDataModel()
                {
                    deviceName = row.GetCell(13).StringCellValue,
                    timeSpan = "2021-一季度",
                    count = Convert.ToInt32(row.GetCell(16).NumericCellValue),
                    s3 = Convert.ToInt32(row.GetCell(17).NumericCellValue),
                    s4 = Convert.ToInt32(row.GetCell(18).NumericCellValue),
                    s5 = Convert.ToInt32(row.GetCell(19).NumericCellValue),
                    s6 = Convert.ToInt32(row.GetCell(20).NumericCellValue),
                    s7 = Convert.ToInt32(row.GetCell(21).NumericCellValue),
                    s8 = Convert.ToInt32(row.GetCell(22).NumericCellValue),
                    s9 = Convert.ToInt32(row.GetCell(23).NumericCellValue),
                    s10 = Convert.ToInt32(row.GetCell(24).NumericCellValue),
                    s11 = Convert.ToInt32(row.GetCell(25).NumericCellValue),
                    s12 = Convert.ToInt32(row.GetCell(26).NumericCellValue),
                    s13 = Convert.ToInt32(row.GetCell(27).NumericCellValue),
                    s14 = Convert.ToInt32(row.GetCell(28).NumericCellValue),
                    s15 = Convert.ToInt32(row.GetCell(29).NumericCellValue),
                    s16 = Convert.ToInt32(row.GetCell(30).NumericCellValue),
                    s17 = Convert.ToInt32(row.GetCell(31).NumericCellValue),
                    s18 = Convert.ToInt32(row.GetCell(32).NumericCellValue),
                    s19 = Convert.ToInt32(row.GetCell(33).NumericCellValue),
                    s20 = Convert.ToInt32(row.GetCell(34).NumericCellValue),
                    produceFactor = row.GetCell(35).NumericCellValue
                });
            }
            return models;
        }

        public List<EfficiencyTableModel> readSeasonalEfficiencyTableExcel()
        {
            List<EfficiencyTableModel> models = new List<EfficiencyTableModel>();
            XSSFWorkbook workbook = new XSSFWorkbook(fileName);
            var sheet = workbook.GetSheetAt(0);
            for (int i = 1; i < 100; i++)
            {
                var row = sheet.GetRow(i);
                models.Add(
                    new EfficiencyTableModel()
                    {
                        project = "baoying",
                        projectId = (int)getDoubleCellValue(row.GetCell(1)),
                        deviceName = row.GetCell(2).StringCellValue,
                        manufactor = row.GetCell(3).StringCellValue,
                        device_no = row.GetCell(4).StringCellValue,
                        count = (int)row.GetCell(5).NumericCellValue,
                        efficiency100 = getDoubleCellValue(row.GetCell(6)),
                        efficiency50 = getDoubleCellValue(row.GetCell(7)),
                        efficiency25 = getDoubleCellValue(row.GetCell(8)),
                        efficiency = getDoubleCellValue(row.GetCell(9))
                    });
            }
            for (int i = 101; i < 443; i++)
            {
                var row = sheet.GetRow(i);
                models.Add(
                    new EfficiencyTableModel()
                    {
                        project = "caojing",
                        projectId = (int)getDoubleCellValue(row.GetCell(1)),
                        deviceName = row.GetCell(2).StringCellValue,
                        manufactor = row.GetCell(3).StringCellValue,
                        device_no = row.GetCell(4).StringCellValue,
                        count = (int)row.GetCell(5).NumericCellValue,
                        efficiency100 = getDoubleCellValue(row.GetCell(6)),
                        efficiency50 = getDoubleCellValue(row.GetCell(7)),
                        efficiency25 = getDoubleCellValue(row.GetCell(8)),
                        efficiency = getDoubleCellValue(row.GetCell(9))
                    });
            }
            for (int i = 444; i < 544; i++)
            {
                var row = sheet.GetRow(i);
                models.Add(
                    new EfficiencyTableModel()
                    {
                        project = "dongtai",
                        projectId = (int)getDoubleCellValue(row.GetCell(1)),
                        deviceName = row.GetCell(2).StringCellValue,
                        manufactor = row.GetCell(3).StringCellValue,
                        device_no = row.GetCell(4).StringCellValue,
                        count = (int)row.GetCell(5).NumericCellValue,
                        efficiency100 = getDoubleCellValue(row.GetCell(6)),
                        efficiency50 = getDoubleCellValue(row.GetCell(7)),
                        efficiency25 = getDoubleCellValue(row.GetCell(8)),
                        efficiency = getDoubleCellValue(row.GetCell(9))
                    });
            }
            for (int i = 545; i < 565; i++)
            {
                var row = sheet.GetRow(i);
                models.Add(
                    new EfficiencyTableModel()
                    {
                        project = "fengxian",
                        projectId = (int)getDoubleCellValue(row.GetCell(1)),
                        deviceName = row.GetCell(2).StringCellValue,
                        manufactor = row.GetCell(3).StringCellValue,
                        device_no = row.GetCell(4).StringCellValue,
                        count = (int)row.GetCell(5).NumericCellValue,
                        efficiency100 = getDoubleCellValue(row.GetCell(6)),
                        efficiency50 = getDoubleCellValue(row.GetCell(7)),
                        efficiency25 = getDoubleCellValue(row.GetCell(8)),
                        efficiency = getDoubleCellValue(row.GetCell(9))
                    });
            }
            for (int i = 566; i < 1140; i++)
            {
                var row = sheet.GetRow(i);
                models.Add(
                    new EfficiencyTableModel()
                    {
                        project = "qihe",
                        projectId = (int)getDoubleCellValue(row.GetCell(1)),
                        deviceName = row.GetCell(2).StringCellValue,
                        manufactor = row.GetCell(3).StringCellValue,
                        device_no = row.GetCell(4).StringCellValue,
                        count = (int)row.GetCell(5).NumericCellValue,
                        efficiency100 = getDoubleCellValue(row.GetCell(6)),
                        efficiency50 = getDoubleCellValue(row.GetCell(7)),
                        efficiency25 = getDoubleCellValue(row.GetCell(8)),
                        efficiency = getDoubleCellValue(row.GetCell(9))
                    });
            }
            for (int i = 1141; i < 1181; i++)
            {
                var row = sheet.GetRow(i);
                models.Add(
                    new EfficiencyTableModel()
                    {
                        project = "xiaogan",
                        projectId = (int)getDoubleCellValue(row.GetCell(1)),
                        deviceName = row.GetCell(2).StringCellValue,
                        manufactor = row.GetCell(3).StringCellValue,
                        device_no = row.GetCell(4).StringCellValue,
                        count = (int)row.GetCell(5).NumericCellValue,
                        efficiency100 = getDoubleCellValue(row.GetCell(6)),
                        efficiency50 = getDoubleCellValue(row.GetCell(7)),
                        efficiency25 = getDoubleCellValue(row.GetCell(8)),
                        efficiency = getDoubleCellValue(row.GetCell(9))
                    });
            }
            return models;
        }

        private IDataModel deserializeExcelRow(ISheet sheet, int rowNumber,Projects projects)
        {
            switch (projects)
            {
                case Projects.DONGTAI:
                    return deserializeDongTaiExcelRow(sheet, rowNumber);
                case Projects.ROOF_PHOTOVOLTAIC:
                    return deserializeRoofPhotovoltaicExcelRow(sheet,rowNumber);
                case Projects.ROOF_WEATHER:
                    return deserializeRoofWeatherExcelRow(sheet, rowNumber);
                default:
                    throw new Exception("项目的参数不正确！");
            }
        }

        private RoofPhotovoltaicModel deserializeRoofPhotovoltaicExcelRow(ISheet sheet,int rowNumber)
        {
            RoofPhotovoltaicModel model = new RoofPhotovoltaicModel();
            var indexs = LocalRoofIndexs.photovoltaicIndexs;
            try
            {
                var row = sheet.GetRow(rowNumber);
                model.time = row.GetCell(0).StringCellValue;
                model.converterData = getDataList(row, indexs.startIndex, indexs.endIndex);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                Console.WriteLine(e.StackTrace);
            }
            return model;
        }

        private RoofWeatherModel deserializeRoofWeatherExcelRow(ISheet sheet, int rowNumber)
        {
            RoofWeatherModel model = new RoofWeatherModel();
            var indexs = LocalRoofIndexs.weatherIndexs;
            try
            {
                var row = sheet.GetRow(rowNumber);
                model.time = addTime(row.GetCell(0).StringCellValue);
                model.weatherData = getDataList(row, indexs.startIndex, indexs.endIndex);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                Console.WriteLine(e.StackTrace);
            }
            return model;
        }

        private DongTaiDataModel deserializeDongTaiExcelRow(ISheet sheet, int rowNumber)
        {
            DongTaiDataModel model = new DongTaiDataModel();

            try
            {
                var row = sheet.GetRow(rowNumber);
                model.createTime = row.GetCell(0).StringCellValue + ":00";
                //解析B逆变器的电力信息
                model.converterBPower = getDataList(row, DongTaiIndexs.dongTaiConverterBPowerIndexs.startIndex, DongTaiIndexs.dongTaiConverterBPowerIndexs.endIndex);
                //解析B逆变器的电能信息
                model.converterBEnergy = getDataList(row, DongTaiIndexs.dongTaiConverterBEnergyIndexs.startIndex, DongTaiIndexs.dongTaiConverterBEnergyIndexs.endIndex);
                //解析B逆变器的电能质量信息
                model.converterBQuality = getDataList(row, DongTaiIndexs.dongTaiConverterBQualityIndexs.startIndex, DongTaiIndexs.dongTaiConverterBQualityIndexs.endIndex);
                //解析A逆变器的电力信息
                model.converterAPower = getDataList(row, DongTaiIndexs.dongTaiConverterAPowerIndexs.startIndex, DongTaiIndexs.dongTaiConverterAPowerIndexs.endIndex);
                //解析A逆变器的电能信息
                model.converterAEnergy = getDataList(row, DongTaiIndexs.dongTaiConverterAEnergyIndexs.startIndex, DongTaiIndexs.dongTaiConverterAEnergyIndexs.endIndex);
                //解析A逆变器的电能质量信息
                model.converterAQuality = getDataList(row, DongTaiIndexs.dongTaiConverterAQualityIndexs.startIndex, DongTaiIndexs.dongTaiConverterAQualityIndexs.endIndex);
                //解析A逆变器的电流信息
                model.converterACurrent = getDataList(row, DongTaiIndexs.dongTaiConverterACurrentIndexs.startIndex, DongTaiIndexs.dongTaiConverterACurrentIndexs.endIndex);
                //解析B逆变器的电流信息
                model.converterBCurrent = getDataList(row, DongTaiIndexs.dongTaiConverterBCurrentIndexs.startIndex, DongTaiIndexs.dongTaiConverterBCurrentIndexs.endIndex);
                //解析逆变器的直流电压
                model.converterVolta = getDataList(row, DongTaiIndexs.dongTaiConverterVoltaIndexs.startIndex, DongTaiIndexs.dongTaiConverterVoltaIndexs.endIndex);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                Console.WriteLine(e.StackTrace);
            }
            return model;
        }

        private List<long> getDataList(IRow row, int startIndex, int endIndex)
        {
            dataList = new List<long>();
            try
            {
                var firstCell = row.GetCell(startIndex);
                if (firstCell.CellType != CellType.String && firstCell.CellType != CellType.Formula)
                {
                    dataList = new List<long>();
                    if (startIndex == 471)
                    {
                        dataList.Add(getCellValue(row.GetCell(473)));
                        dataList.Add(getCellValue(row.GetCell(472)));
                        dataList.Add(getCellValue(row.GetCell(471)));
                        dataList.Add(getCellValue(row.GetCell(476)));
                        dataList.Add(getCellValue(row.GetCell(475)));
                        dataList.Add(getCellValue(row.GetCell(474)));
                        dataList.Add(getCellValue(row.GetCell(478)));
                        dataList.Add(getCellValue(row.GetCell(477)));
                    }
                    else if (startIndex == 479)
                    {
                        dataList.Add(getCellValue(row.GetCell(481)));
                        dataList.Add(getCellValue(row.GetCell(480)));
                        dataList.Add(getCellValue(row.GetCell(479)));
                        dataList.Add(getCellValue(row.GetCell(484)));
                        dataList.Add(getCellValue(row.GetCell(483)));
                        dataList.Add(getCellValue(row.GetCell(482)));
                        dataList.Add(getCellValue(row.GetCell(486)));
                        dataList.Add(getCellValue(row.GetCell(485)));
                    }
                    else
                    {
                        for (int i = startIndex; i < endIndex; i++)
                        {
                            dataList.Add(getCellValue(row.GetCell(i)));
                        }
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                Console.WriteLine(e.StackTrace);
            }
            return dataList;
        }
        private long getCellValue(ICell cell)
        {
            if (cell != null && cell.CellType != CellType.String && cell.CellType != CellType.Formula)
            {
                return Convert.ToInt64(cell.NumericCellValue);
            }
            else
            {
                return 0L;
            }
        }

        private double getDoubleCellValue(ICell cell)
        {
            if (cell != null && cell.CellType != CellType.String && cell.CellType != CellType.Formula)
            {
                return cell.NumericCellValue;
            }
            else
            {
                return 0.0;
            }
        }

        public static void writeDataToFile(List<string> timeList,List<List<long>> data, List<string> headers, string fileName,string projectName)
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            var sheet = workbook.CreateSheet(projectName);
            var firstRow = sheet.CreateRow(0);
            int readerIndex = 1;
            int rowIndex = 0;
            #region 写入表头
            foreach (var header in headers)
            {
                firstRow.CreateCell(rowIndex++).SetCellValue(header);
            }
            #endregion
            //然后写入内容
            rowIndex = 0;
            for (int i = 0; i < data.Count; i++)
            {
                var item = data[i];
                var row = sheet.CreateRow(readerIndex++);
                if (timeList != null)
                {
                    row.CreateCell(rowIndex++).SetCellValue(timeList[i]);
                }
                foreach (var cellData in item)
                {
                    row.CreateCell(rowIndex++).SetCellValue(cellData);
                }
                rowIndex = 0;
            }
            //导出成文件
            FileInfo info = new FileInfo(fileName);
            if (!info.Directory.Exists)
            {
                info.Directory.Create();
            }
            workbook.Write(new FileStream(fileName, FileMode.Create));
            workbook.Close();
        }

        //因为气象站的时间跟标准时间差六分钟多,所以这里要给气象站的时间加上这六分钟多的时间
        private string addTime(string rawTime)
        {
            //var datetime = rawTime.Split(' ');
            //var date = datetime[0].Split('-');
            //var time = datetime[1].Split(':');
            //Convert.ToInt32(date[0]), Convert.ToInt32(date[1]), Convert.ToInt32(date[2]), time[0], time[1], time[2]
            if (rawTime == "0-0-0 0:0:0")
            {
                return "2000-01-01 00:00:00";
            }
            try
            {
                if (rawTime.IndexOf('-') == 2)
                {
                    rawTime = "20" + rawTime;
                }
                var now = Convert.ToDateTime(rawTime);
                var realTime = now.AddSeconds(376);
                return realTime.ToString("yyyy-MM-dd HH:mm:ss");
            }
            catch (Exception ex)
            {
                Console.WriteLine(fileName);
                Console.WriteLine(rawTime);
                Console.WriteLine(ex.StackTrace);
                Console.WriteLine(ex.Message);
                return "";
            }
            
        }
    }
}
